本篇放上獲得最上階產品料號的作法
1.建立報表
2.設定SQL語法
3.設定Method
4.設定XSLT
參考程式碼
https://github.com/panda0909/Aras-PLM-Exercise/tree/master/Report/Where%20Used%20Top%20Level%20Product

1.建立報表

2.設定SQL語法-用來查詢最上階層料號

---遞迴查詢
with [bom_new] as (
select pbom.SOURCE_ID as tr_sid,pbom.RELATED_ID as tr_rid,p.ITEM_NUMBER as parent,pr.ITEM_NUMBER as son ,pr.NAME,1 as level from [innovator].[PART_BOM] as pbom
inner join [innovator].[part] as p on pbom.SOURCE_ID=p.id
inner join [innovator].[part] as pr on pbom.RELATED_ID=pr.id
where pr.id=@PART_ID and p.IS_CURRENT = '1'
---想要查詢的零件料號

---聯集所有上層料號
UNION ALL
select pbom2.SOURCE_ID as tr_sid,pbom2.RELATED_ID as tr_rid,p.ITEM_NUMBER as parent,pr.ITEM_NUMBER as son,pr.NAME,level + 1
from [innovator].[PART_BOM] as pbom2
inner join [bom_new] as bnew on bnew.tr_sid=pbom2.RELATED_ID
inner join [innovator].part as p on pbom2.SOURCE_ID =p.id
inner join [innovator].[part] as pr on pbom2.RELATED_ID=pr.id
where p.IS_CURRENT='1'
)
---料號中開頭為P成品件
select ta.parent as item_number from [bom_new] as ta where ta.parent like 'P%'

3.設定Method-透用Method呼叫查詢SQL

private Item GetAllItems(string part_id)
{
    Item callframe = inn.newItem("SQL", "SQL PROCESS");
    callframe.setProperty("name", "JPC_WhereUsedTopLevelBOM");
    callframe.setProperty("PROCESS", "CALL");
    callframe.setProperty("ARG1", part_id);
    
    return callframe.apply();
}